package com.scau.RBAC.dao.impl;

import com.scau.RBAC.dao.PermissionDao;
import com.scau.RBAC.model.entity.Permission;
import com.scau.RBAC.util.DBConnectionPool;
import com.scau.RBAC.util.DatabaseException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * PostgreSQL数据库的PermissionDao实现
 */
public class PostgreSqlPermissionDao implements PermissionDao {
    private final QueryRunner queryRunner;

    public PostgreSqlPermissionDao() {
        DataSource dataSource = DBConnectionPool.getDataSource();
        this.queryRunner = new QueryRunner(dataSource);
    }

    @Override
    public int insert(Permission entity) {
        try {
            return queryRunner.update(
                    "INSERT INTO permissions(permission_name, permission_code, description, parent_id, resource_path, sort_order, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    entity.getPermissionName(), entity.getPermissionCode(), entity.getDescription(), entity.getParentId(),
                    entity.getResourcePath(), entity.getSortOrder(), entity.getStatus(), entity.getCreatedTime(),
                    entity.getUpdatedTime(), entity.getCreatedBy(), entity.getUpdatedBy()
            );
        } catch (SQLException e) {
            throw new DatabaseException("插入权限失败", e);
        }
    }

    @Override
    public int batchInsert(List<Permission> entities) {
        try {
            Object[][] params = new Object[entities.size()][11];
            for (int i = 0; i < entities.size(); i++) {
                Permission permission = entities.get(i);
                params[i][0] = permission.getPermissionName();
                params[i][1] = permission.getPermissionCode();
                params[i][2] = permission.getDescription();
                params[i][3] = permission.getParentId();
                params[i][4] = permission.getResourcePath();
                params[i][5] = permission.getSortOrder();
                params[i][6] = permission.getStatus();
                params[i][7] = permission.getCreatedTime();
                params[i][8] = permission.getUpdatedTime();
                params[i][9] = permission.getCreatedBy();
                params[i][10] = permission.getUpdatedBy();
            }

            int[] results = queryRunner.batch(
                    "INSERT INTO permissions(permission_name, permission_code, description, parent_id, resource_path, sort_order, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    params
            );

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            throw new DatabaseException("批量插入权限失败", e);
        }
    }

    @Override
    public int deleteById(Long id) {
        try {
            return queryRunner.update("DELETE FROM permissions WHERE id = ?", id);
        } catch (SQLException e) {
            throw new DatabaseException("删除权限失败", e);
        }
    }

    @Override
    public int batchDelete(List<Long> ids) {
        try {
            Object[][] params = new Object[ids.size()][1];
            for (int i = 0; i < ids.size(); i++) {
                params[i][0] = ids.get(i);
            }

            int[] results = queryRunner.batch("DELETE FROM permissions WHERE id = ?", params);

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            throw new DatabaseException("批量删除权限失败", e);
        }
    }

    @Override
    public int update(Permission entity) {
        try {
            return queryRunner.update(
                    "UPDATE permissions SET permission_name = ?, permission_code = ?, description = ?, parent_id = ?, resource_path = ?, sort_order = ?, status = ?, updated_time = ?, updated_by = ? WHERE id = ?",
                    entity.getPermissionName(), entity.getPermissionCode(), entity.getDescription(), entity.getParentId(),
                    entity.getResourcePath(), entity.getSortOrder(), entity.getStatus(), entity.getUpdatedTime(),
                    entity.getUpdatedBy(), entity.getId()
            );
        } catch (SQLException e) {
            throw new DatabaseException("更新权限失败", e);
        }
    }

    @Override
    public Permission findById(Long id) {
        try {
            return queryRunner.query("SELECT * FROM permissions WHERE id = ?", new BeanHandler<>(Permission.class), id);
        } catch (SQLException e) {
            throw new DatabaseException("根据ID查询权限失败", e);
        }
    }

    @Override
    public List<Permission> findAll() {
        try {
            return queryRunner.query("SELECT * FROM permissions", new BeanListHandler<>(Permission.class));
        } catch (SQLException e) {
            throw new DatabaseException("查询所有权限失败", e);
        }
    }

    @Override
    public List<Permission> findByPage(Map<String, Object> params, int pageNum, int pageSize) {
        try {
            StringBuilder sql = new StringBuilder("SELECT * FROM permissions WHERE 1=1");
            Object[] queryParams = buildQueryParams(sql, params);

            sql.append(" LIMIT ? OFFSET ?");
            Object[] finalParams = new Object[queryParams.length + 2];
            System.arraycopy(queryParams, 0, finalParams, 0, queryParams.length);
            finalParams[queryParams.length] = pageSize;
            finalParams[queryParams.length + 1] = (pageNum - 1) * pageSize;

            return queryRunner.query(sql.toString(), new BeanListHandler<>(Permission.class), finalParams);
        } catch (SQLException e) {
            throw new DatabaseException("分页查询权限失败", e);
        }
    }

    @Override
    public long count(Map<String, Object> params) {
        try {
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM permissions WHERE 1=1");
            Object[] queryParams = buildQueryParams(sql, params);

            return queryRunner.query(sql.toString(), rs -> {
                if (rs.next()) {
                    return rs.getLong(1);
                }
                return 0L;
            }, queryParams);
        } catch (SQLException e) {
            throw new DatabaseException("统计权限数量失败", e);
        }
    }

    @Override
    public List<Permission> findByRoleId(Long roleId) {
        try {
            return queryRunner.query(
                    "SELECT p.* FROM permissions p INNER JOIN role_permissions rp ON p.id = rp.permission_id WHERE rp.role_id = ?",
                    new BeanListHandler<>(Permission.class), roleId
            );
        } catch (SQLException e) {
            throw new DatabaseException("根据角色ID查询权限失败", e);
        }
    }

    @Override
    public List<Permission> findByUserId(Long userId) {
        try {
            return queryRunner.query(
                    "SELECT DISTINCT p.* FROM permissions p INNER JOIN role_permissions rp ON p.id = rp.permission_id " +
                            "INNER JOIN user_roles ur ON rp.role_id = ur.role_id WHERE ur.user_id = ?",
                    new BeanListHandler<>(Permission.class), userId
            );
        } catch (SQLException e) {
            throw new DatabaseException("根据用户ID查询权限失败", e);
        }
    }

    @Override
    public List<Permission> findPermissionTree(Long parentId) {
        try {
            // PostgreSQL支持WITH RECURSIVE进行递归查询
            String sql = """
                WITH RECURSIVE permission_tree(id, permission_name, permission_code, description, parent_id, resource_path, sort_order, status, created_time, updated_time, created_by, updated_by, level)
                AS (
                    SELECT id, permission_name, permission_code, description, parent_id, resource_path, sort_order, status, created_time, updated_time, created_by, updated_by, 1 AS level
                    FROM permissions
                    WHERE parent_id = ? OR (parent_id IS NULL AND ? IS NULL)
                    UNION ALL
                    SELECT p.id, p.permission_name, p.permission_code, p.description, p.parent_id, p.resource_path, p.sort_order, p.status, p.created_time, p.updated_time, p.created_by, p.updated_by, pt.level + 1
                    FROM permissions p
                    INNER JOIN permission_tree pt ON p.parent_id = pt.id
                )
                SELECT * FROM permission_tree ORDER BY level, sort_order
            """;

            return queryRunner.query(sql, new BeanListHandler<>(Permission.class), parentId, parentId);
        } catch (SQLException e) {
            throw new DatabaseException("查询权限树失败", e);
        }
    }

    private Object[] buildQueryParams(StringBuilder sql, Map<String, Object> params) {
        // 简化实现
        return new Object[0];
    }
}